const router = require('koa-router')()
const connection = require('./db'); // 导入数据库连接对象
const fs = require('fs');
const query1 = 'SELECT * FROM `student-course`';
const query = 'SELECT * FROM course WHERE attribute = "选修"';

router.get("/searchaddcourse", async (ctx, next) => {
  const { keyword } = ctx.query; // 从请求参数中获取关键词
  console.log('从请求参数中获取关键词' + keyword);
  try {
    // 构建 SQL 查询语句
    const searchQuery = `SELECT * FROM course WHERE (coursename LIKE '%${keyword}%' OR courseid LIKE '%${keyword}%') AND attribute = '选修'`;

    // 使用 Promise 封装数据库查询
    const queryAsync = () => {
      return new Promise((resolve, reject) => {
        connection.query(searchQuery, function (error, results, fields) {
          if (error) {
            reject(error);
          } else {
            resolve(results);
          }
        });
      });
    };

    // 使用 await 等待查询结果
    const searchResults = await queryAsync();

    console.log('匹配到的数据', searchResults);

    // 返回搜索结果
    ctx.body = {
      results: searchResults,
      length: searchResults.length  // 返回结果的长度
    };
  } catch (error) {
    // 处理错误
    console.error(error);
    ctx.status = 500;
    ctx.body = "Internal Server Error";
  }
});
router.post('/increasecourse', async (ctx) => {
  const courseid = ctx.request.body.courseid;
  const studentid = ctx.request.body.studentid;
  const time = ctx.request.body.time;

  try {
    // 使用 Promise 封装数据库查询
    const queryAsync = (sql) => {
      return new Promise((resolve, reject) => {
        connection.query(sql, function (error, results, fields) {
          if (error) {
            reject(error);
          } else {
            resolve(results);
          }
        });
      });
    };

    // 检查是否已经存在该课程
    const courseExistsSql = `SELECT * FROM \`student-course\` WHERE courseid = ${courseid} AND studentid = ${studentid}`;
    const courseExistsResult = await queryAsync(courseExistsSql);

    if (courseExistsResult.length > 0) {
      // 如果已经存在该课程，返回 '课程已添加'
      ctx.body = '课程已添加';
    } else {
      // 查询以检查学生是否已在给定时间有课程
      const checkTimeConflictSql = `
        SELECT sc.courseid, c.time 
        FROM \`student-course\` sc
        INNER JOIN course c ON sc.courseid = c.courseid
        WHERE sc.studentid = ${studentid}
      `;

      // 等待时间冲突检查查询的结果
      const timeConflictList = await queryAsync(checkTimeConflictSql);

      // 检查是否存在时间冲突
      const hasTimeConflict = timeConflictList.some(item => {
        // 将时间字符串解析为具体时间
        const existingTimeRange = item.time.split(', '); // 拆分字符串为具体时间范围
        const newTimeRange = time.split(', '); // 拆分字符串为具体时间范围
      
        // 检查每个时间范围是否存在冲突
        return existingTimeRange.some(existingTime => {
          return newTimeRange.some(newTime => {
            const [existingDay, existingHours] = existingTime.split(' '); // 拆分日期和时间段
            const [newDay, newHours] = newTime.split(' '); // 拆分日期和时间段
      
            // 如果星期几相同并且时间段存在重叠，则返回 true 表示存在冲突
            return (existingDay === newDay && existingHours === newHours);
          });
        });
      });

      if (hasTimeConflict) {
        ctx.body = '课程时间冲突，是否继续添加';
      } else {
        // 如果没有时间冲突且课程不存在，继续插入操作
        const insertResult = await connection.query(
          'INSERT INTO `student-course` (courseid, studentid) VALUES (?, ?)',
          [courseid, studentid]
        );

        console.log('Inserted into database:', insertResult);

        // 更新 'course' 表中的 'num'
        const updateNumSql = `UPDATE course SET num = num + 1 WHERE courseid = ${courseid}`;
        connection.query(updateNumSql, (error, results, fields) => {
          if (error) {
            console.error('Error updating num:', error.message);
          } else {
            console.log('Successfully incremented the value of num for the course with ID', courseid);
          }
        });

        ctx.body = '添加成功';
      }

  router.post('/increasecourseonOk', async (ctx) => {
  const courseid = ctx.request.body.courseid;
  const studentid = ctx.request.body.studentid;
  const insertResult = await connection.query(
    'INSERT INTO `student-course` (courseid, studentid) VALUES (?, ?)',
    [courseid, studentid]
  );
  const updateNumSql = `UPDATE course SET num = num + 1 WHERE courseid = ${courseid}`;
        connection.query(updateNumSql, (error, results, fields) => {
          if (error) {
            console.error('Error updating num:', error.message);
          } else {
            console.log('Successfully incremented the value of num for the course with ID', courseid);
          }
        });

  console.log('Inserted into database:', insertResult);
  ctx.body = '添加成功';
})
    }
  } catch (error) {
    // 处理错误
    console.error(error);
    ctx.status = 500;
    ctx.body = 'Internal Server Error';
  }
});



router.get("/getchoosecourselist", async (ctx, next) => {
  try {
    const addValue = await new Promise((resolve, reject) => {
      connection.query('SELECT * FROM master WHERE id = 1', (error, results) => {
        if (error) {
          console.error(error);
          reject(error);
        } else {
          resolve(results[0].add);
        }
      });
    });

    console.log("add的值", addValue);

    let request = ctx.request;
    let reqquery = request.query;

    const queryAsync = () => {
      return new Promise((resolve, reject) => {
        connection.query(query, function (error, results, fields) {
          if (error) {
            reject(error);
          } else {
            resolve(results);
          }
        });
      });
    };

    const list = await queryAsync();
    const parsedList = JSON.parse(JSON.stringify(list));
    const page = reqquery.page;

    if (addValue == 1) {
      ctx.body = {
        list: parsedList,
        length: parsedList.length
      };
    } else {
      ctx.body = "现在是非选课时间";
    }
  } catch (error) {
    console.error(error);
    ctx.status = 500;
  }
});


router.get("/getchoosedcourselist", async (ctx, next) => {
  try {
    const delValue = await new Promise((resolve, reject) => {
      connection.query('SELECT * FROM master WHERE id = 1', (error, results) => {
        if (error) {
          console.error(error);
          reject(error);
        } else {
          resolve(results[0].del);
        }
      });
    });

    console.log("del的值", delValue);

    if (delValue == 1) {
      let request = ctx.request;
      let reqquery = request.query;
      console.log(reqquery.page);
      console.log(reqquery.studentid);
      console.log(reqquery);

      const studentId = reqquery.studentid;
      const query3 = `SELECT * FROM \`student-course\` WHERE studentid = ${studentId}`;
      console.log(query3);

      try {
        // 使用 Promise 封装数据库查询
        const queryAsync = () => {
          return new Promise((resolve, reject) => {
            connection.query(query3, function (error, results, fields) {
              if (error) {
                reject(error);
              } else {
                resolve(results);
              }
            });
          });
        };

        // 使用 await 等待查询结果
        const list = await queryAsync();
        const parsedList = JSON.parse(JSON.stringify(list));

        const page = reqquery.page;
        const perpage = reqquery.perpage;
        console.log('页码:', page, '每页数量:', perpage);
        const courseCollection = [];

        for (const item of parsedList) {
          const courseId = item.courseid;
          const query5 = `SELECT * FROM \`course\` WHERE courseid = ${courseId}`;

          try {
            // 使用 Promise 封装数据库查询
            const queryAsync = () => {
              return new Promise((resolve, reject) => {
                connection.query(query5, function (error, results, fields) {
                  if (error) {
                    reject(error);
                  } else {
                    resolve(results);
                  }
                });
              });
            };

            // 使用 await 等待查询结果
            const list = await queryAsync();
            const cList = JSON.parse(JSON.stringify(list));
            courseCollection.push(cList[0]);

          } catch (error) {
            console.error(error);
          }
        }

        console.log(courseCollection);
        ctx.body = {
          list: courseCollection,
          length: courseCollection.length
        };

      } catch (error) {
        console.error(error);
        ctx.status = 500;
        ctx.body = "Internal Server Error";
      }
    } else {
      ctx.body = "现在是非选课时间";
    }

  } catch (error) {
    console.error(error);
    ctx.status = 500;
    ctx.body = "Internal Server Error";
  }
});



router.post('/Deselectcourse', async (ctx) => {
  const courseid = ctx.request.body.courseid;
  const studentid = ctx.request.body.studentid;

  try {
    // 使用 Promise 封装数据库删除操作
    const deleteCourseAsync = () => {
      return new Promise((resolve, reject) => {
        const query6 = `DELETE FROM \`student-course\` WHERE courseid = ? AND studentid = ?`;
        connection.query(query6, [courseid, studentid], function (error, results, fields) {
          if (error) {
            reject(error);
          } else {
            resolve(results);
          }
        });
      });
    };

    // 执行删除操作
    await deleteCourseAsync();
    const sql9 = `UPDATE course SET num = num - 1 WHERE courseid = ${courseid}`;

    // Execute the SQL query
    connection.query(sql9, (error, results, fields) => {
      if (error) {
        console.error('Error updating num:', error.message);
      } else {
        console.log('Successfully incremented the value of num for the course with ID', courseid);
      }
    });
    // 返回成功的响应
    ctx.status = 200;
    ctx.body = { message: '删除成功' };
  } catch (error) {
    // 处理错误
    ctx.status = 500; // 设置状态码为服务器错误
    ctx.body = { error: '删除失败' }; // 返回错误信息
  }

});

router.get("/getstudenthome", async (ctx, next) => {
  let request = ctx.request;
  let reqquery = request.query;
  console.log(reqquery.studentid);
  console.log(reqquery);

  const studentId = reqquery.studentid;
  const query7 = `SELECT * FROM \`student\` WHERE studentid = ${studentId}`;
  console.log(query7);
  try {
    // 使用 Promise 封装数据库查询
    const queryAsync = () => {
      return new Promise((resolve, reject) => {
        connection.query(query7, function (error, results, fields) {
          if (error) {
            reject(error);
          } else {
            resolve(results);
          }
        });
      });
    };

    // 使用 await 等待查询结果
    const list = await queryAsync();
    //console.log(JSON.stringify(list));
    const parsedList = JSON.parse(JSON.stringify(list));
    console.log(parsedList);

    ctx.body = {
      list: parsedList,
    };
    // 打印整个集合
  } catch (error) {
    // 处理错误
    console.error(error);
    ctx.status = 500;
    ctx.body = "Internal Server Error";
  }
});

router.post('/changestudentpassword', async (ctx, next) => {
  // 使用 ctx.query 访问查询参数
  const Id = ctx.request.body.id;
  const values = ctx.request.body.values;
  const newpassword = values.newpassword;
  console.log('收到的前端数据：', Id, newpassword);
  try {
    // 执行数据库更新操作
    const updateQuery = 'UPDATE student SET password = ? WHERE studentid = ?';
    await connection.query(updateQuery, [newpassword, Id]);

    // 检查是否成功更新数据
    console.log('成功更新学生数据');
    ctx.body = '成功更新学生数据';
  } catch (error) {
    console.error('更新学生数据时出现错误：', error);
    ctx.status = 500;
    ctx.body = '更新学生数据时出现错误';
  }
});

router.post('/studentupload', async (ctx) => {
  const file = ctx.request.files.file; // 文件将被存储在ctx.request.files中
  const studentId = ctx.request.body.studentid;
  const avatar = ctx.request.body.avatar;
  // 执行适当的文件处理逻辑
  const newFilename = file['newFilename'];
  console.log('新文件名:', newFilename, studentId, avatar);
  const filePath = './routes/uploads/' + avatar;
  fs.unlink(filePath, (err) => {
    if (err) {
      console.error('文件删除失败', err);
      return;
    }
    console.log('文件删除成功');
  });
  await connection.query('UPDATE student SET avatar = ? WHERE studentid = ?', [newFilename, studentId]);
  ctx.body = 'File uploaded successfully';
})

router.post('/teacherupload', async (ctx) => {
  const { imageData } = ctx.request.body;
  // 在这里处理接收到的图片信息
  console.log('接收到的图片信息:',ctx.request.body);
  const filePath = './routes/uploads/' +imageData;
  fs.unlink(filePath, (err) => {
    if (err) {
      console.error('文件删除失败', err);
      return;
    }
    console.log('文件删除成功');
  });
  // 执行其他操作，例如删除文件等
  ctx.body = 'File uploaded successfully';
});


router.post('/studentAssignment', async (ctx, next) => {
  const { courseid, grade, className, major } = ctx.request.body;
  console.log(courseid, grade, className, major);

  try {
    const courseCheckResult = await new Promise((resolve, reject) => {
      connection.query('SELECT * FROM course WHERE courseid = ?', [courseid], (error, result) => {
        if (error) {
          reject(error);
        } else {
          resolve(result);
        }
      });
    });

    if (courseCheckResult.length === 0) {
      // 如果 courseid 不存在，返回相应信息
      ctx.status = 404;
      ctx.body = { message: '课程不存在' };
      return;
    }

    // 从表student表里获取attribute检查是否为必修
    const courseattributeCheckResult = await new Promise((resolve, reject) => {
      connection.query('SELECT attribute FROM course WHERE courseid = ? AND attribute = "必修"', [courseid], (error, result) => {
        if (error) {
          reject(error);
        } else {
          resolve(result);
        }
      });
    });

    if (courseattributeCheckResult.length === 0) {
      // 如果课程不是必修，返回相应信息
      ctx.status = 404; // 可根据需求设置状态码
      ctx.body = { message: '课程不是必修' };
      return;
    }


    // 检查是否有同时满足 grade、className 和 major 条件的记录
const recordCheckResult = await new Promise((resolve, reject) => {
  connection.query('SELECT * FROM student WHERE grade = ? AND class = ? AND major = ?', [grade, className, major], (error, result) => {
    if (error) {
      reject(error);
    } else {
      resolve(result);
    }
  });
});

// 如果没有满足条件的记录，返回相应信息
if (recordCheckResult.length === 0) {
  ctx.status = 404;
  ctx.body = { message: '班级不存在' };
  return;
}
console.log(recordCheckResult.length)
const updateNumResult = await new Promise((resolve, reject) => {
  connection.query('UPDATE course SET num = ? WHERE courseid = ?', [recordCheckResult.length, courseid], (error, result) => {
    if (error) {
      reject(error);
    } else {
      resolve(result);
    }
  });
});if (updateNumResult.affectedRows <= 0) {
  // 处理更新失败的情况
}
// 检查 student-course 表里是否有相同的数据
const existingRecords = await new Promise((resolve, reject) => {
  connection.query('SELECT * FROM `student-course` WHERE courseid = ? AND studentid IN (?)', [courseid, recordCheckResult.map(row => row.studentid)], (error, result) => {
    if (error) {
      reject(error);
    } else {
      resolve(result);
    }
  });
});

// 过滤出不存在的记录并插入到 student-course 表
const newRecords = recordCheckResult.filter(row => {
  const exists = existingRecords.some(existingRow => existingRow.studentid === row.studentid && existingRow.courseid === courseid);
  return !exists;
});

for (const row of newRecords) {
  const insertResult = await new Promise((resolve, reject) => {
    connection.query('INSERT INTO `student-course` (courseid, studentid) VALUES (?, ?)', [courseid, row.studentid], (error, result) => {
      if (error) {
        reject(error);
      } else {
        resolve(result);
      }
    });
  });

  console.log(insertResult);

  if (insertResult.affectedRows <= 0) {
    ctx.status = 500;
    ctx.body = { message: '内部服务器错误' };
    return;
  }
}

ctx.status = 200;
ctx.body = { message: '分配成功' };

  } catch (error) {
    console.error('错误:', error);
    ctx.status = 500;
    ctx.body = { message: '内部服务器错误' };
  }
});


router.post('/courseAssignment', async (ctx, next) => {
  const { courseid, studentid } = ctx.request.body;
  console.log(courseid, studentid);

  try {
    const courseCheckResult = await new Promise((resolve, reject) => {
      connection.query('SELECT * FROM course WHERE courseid = ?', [courseid], (error, result) => {
        if (error) {
          reject(error);
        } else {
          resolve(result);
        }
      });
    });

    if (courseCheckResult.length === 0) {
      // If courseid does not exist, return corresponding message
      ctx.status = 404;
      ctx.body = { message: '课程不存在' };
      return;
    }

    const recordCheckResult = await new Promise((resolve, reject) => {
      connection.query('SELECT * FROM `student-course` WHERE courseid = ? AND studentid = ?', [courseid, studentid], (error, result) => {
        if (error) {
          reject(error);
        } else {
          resolve(result);
        }
      });
    });

    if (recordCheckResult.length > 0) {
      // If a record exists for the given courseid and studentid, return corresponding message
      ctx.status = 400;
      ctx.body = { message: '该学生已选择该课程' };
      return;
    }

    // Add a new entry to the student-course table
    await new Promise((resolve, reject) => {
      connection.query('INSERT INTO `student-course` (courseid, studentid) VALUES (?, ?)', [courseid, studentid], (error, result) => {
        if (error) {
          reject(error);
        } else {
          resolve(result);
        }
      });
    });

    ctx.status = 200;
    ctx.body = { message: '分配成功' };

  } catch (error) {
    console.error('错误:', error);
    ctx.status = 500;
    ctx.body = { message: '内部服务器错误' };
  }
});



module.exports = router;